package com.library.dao.impl;

import com.library.dao.BookDao;
import com.library.entity.BookInfo;
import com.library.utils.DBUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl implements BookDao {
    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    @Override
    public int addBook(BookInfo bookInfo) {
        String sql = "INSERT INTO book_info (name, author, publish, isbn, introduction, language, price, publish_date, type_id, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, bookInfo.getName());
            preparedStatement.setString(2, bookInfo.getAuthor());
            preparedStatement.setString(3, bookInfo.getPublish());
            preparedStatement.setString(4, bookInfo.getIsbn());
            preparedStatement.setString(5, bookInfo.getIntroduction());
            preparedStatement.setString(6, bookInfo.getLanguage());
            preparedStatement.setDouble(7, bookInfo.getPrice());
            preparedStatement.setDate(8, new java.sql.Date(bookInfo.getPublishDate().getTime()));
            preparedStatement.setInt(9, bookInfo.getTypeId());
            preparedStatement.setInt(10, 1); // 默认状态为1（可借）
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
    }

    @Override
    public int updateBook(BookInfo bookInfo) {
        String sql = "UPDATE book_info SET name=?, author=?, publish=?, isbn=?, introduction=?, language=?, price=?, publish_date=?, type_id=? WHERE id=?";
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, bookInfo.getName());
            preparedStatement.setString(2, bookInfo.getAuthor());
            preparedStatement.setString(3, bookInfo.getPublish());
            preparedStatement.setString(4, bookInfo.getIsbn());
            preparedStatement.setString(5, bookInfo.getIntroduction());
            preparedStatement.setString(6, bookInfo.getLanguage());
            preparedStatement.setDouble(7, bookInfo.getPrice());
            preparedStatement.setDate(8, new java.sql.Date(bookInfo.getPublishDate().getTime()));
            preparedStatement.setInt(9, bookInfo.getTypeId());
            preparedStatement.setInt(10, bookInfo.getId());
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
    }

    @Override
    public int deleteBook(int id) {
        String sql = "DELETE FROM book_info WHERE id=?";
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
    }

    @Override
         public List<BookInfo> findAllBooks() {
         List<BookInfo> books = new ArrayList<>();
         String sql = "SELECT * FROM book_info";
         try (Connection connection = DBUtils.getConnection();
              PreparedStatement preparedStatement = connection.prepareStatement(sql);
              ResultSet resultSet = preparedStatement.executeQuery()) {
             while (resultSet.next()) {
                 BookInfo book = new BookInfo();
                 book.setId(resultSet.getInt("id"));
                 book.setName(resultSet.getString("name"));
                 book.setAuthor(resultSet.getString("author"));
                 book.setPublish(resultSet.getString("publish"));
                 book.setIsbn(resultSet.getString("isbn"));
                 book.setIntroduction(resultSet.getString("introduction"));
                 book.setLanguage(resultSet.getString("language"));
                 book.setPrice(resultSet.getDouble("price"));
                 book.setPublishDate(resultSet.getDate("publish_date"));
                 book.setTypeId(resultSet.getInt("type_id"));
                 book.setStatus(resultSet.getInt("status"));
                 books.add(book);
             }
         } catch (SQLException e) {
             e.printStackTrace();
         }
         return books;
     }


    @Override
    public BookInfo findBookById(int id) {
        String sql = "SELECT b.*, t.name as type_name FROM book_info b LEFT JOIN type_info t ON b.type_id = t.id WHERE b.id=?";
        BookInfo book = null;
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                book = getBookFromResultSet(resultSet);
                book.setTypeName(resultSet.getString("type_name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return book;
    }

    @Override
    public List<BookInfo> searchBooks(BookInfo bookInfo) {
        List<BookInfo> books = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT b.*, t.name as type_name FROM book_info b LEFT JOIN type_info t ON b.type_id = t.id WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (bookInfo.getName() != null && !bookInfo.getName().isEmpty()) {
            sql.append(" AND b.name LIKE ?");
            params.add("%" + bookInfo.getName() + "%");
        }
        if (bookInfo.getAuthor() != null && !bookInfo.getAuthor().isEmpty()) {
            sql.append(" AND b.author LIKE ?");
            params.add("%" + bookInfo.getAuthor() + "%");
        }
        if (bookInfo.getIsbn() != null && !bookInfo.getIsbn().isEmpty()) {
            sql.append(" AND b.isbn = ?");
            params.add(bookInfo.getIsbn());
        }
        if (bookInfo.getTypeId() != null && bookInfo.getTypeId() > 0) {
            sql.append(" AND b.type_id = ?");
            params.add(bookInfo.getTypeId());
        }

        try {
            connection = DBUtils.getConnection();
            System.out.println("sql:"+sql.toString());
            preparedStatement = connection.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(i + 1, params.get(i));
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                BookInfo book = getBookFromResultSet(resultSet);
                book.setTypeName(resultSet.getString("type_name"));
                books.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return books;
    }

    @Override
    public List<BookInfo> findAllList() {
        List<BookInfo> books = new ArrayList<>();
        String sql = "SELECT b.*, t.name as type_name FROM book_info b LEFT JOIN type_info t ON b.type_id = t.id";
        try (Connection connection = DBUtils.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery()) {

            while (resultSet.next()) {
                BookInfo book = getBookFromResultSet(resultSet); //
                book.setTypeName(resultSet.getString("type_name"));
                books.add(book);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return books;
    }


    private BookInfo getBookFromResultSet(ResultSet rs) throws SQLException {
        BookInfo book = new BookInfo();
        book.setId(rs.getInt("id"));
        book.setName(rs.getString("name"));
        book.setAuthor(rs.getString("author"));
        book.setPublish(rs.getString("publish"));
        book.setIsbn(rs.getString("isbn"));
        book.setIntroduction(rs.getString("introduction"));
        book.setLanguage(rs.getString("language"));
        book.setPrice(rs.getDouble("price"));
        book.setPublishDate(rs.getDate("publish_date"));
        book.setTypeId(rs.getInt("type_id"));
        book.setStatus(rs.getInt("status"));
        return book;
    }
}